const {
    sqlExec,
    sqlExecInTransaction,
    startTransaction,
    commitTransaction,
    rollbackTransaction
} = require('../db/mysql57');

const minio = require('../db/minio');

const courseDao = {
    /*
     * 获取推荐课程接口
     */
    async getCourse(uid, lastId) {
        let sql = `select
                c.id as id,
                c.name as name,
                c.description as description,
                c.price as price,
                c.photoId as photoId,
                (select count(*) from CourseJoin as cj where cj.uid = ? and cj.courseId = c.id) as isJoin,
                (select count(*) from CourseJoin as cj where cj.courseId = c.id) as learnNum
            from Course as c
            LIMIT 10`;
        let data = [ uid ];
        if (lastId) {
            sql = `select
                    c.id as id,
                    c.name as name,
                    c.description as description,
                    c.price as price,
                    c.photoId as photoId,
                    (select count(*) from CourseJoin as cj where cj.uid = ? and cj.courseId = c.id) as isJoin,
                (select count(*) from CourseJoin as cj where cj.courseId = c.id) as learnNum
                from Course as c
                where id > ?
                LIMIT 10`;
            data = [ uid, lastId ];
        }
        return sqlExec(sql, data);
    },
    /*
     * 查看课程详情接口
     */
    async getCourseInfo(uid, courseId) {
        let sql = `select
                c.id as id,
                c.name as name,
                c.description as description,
                c.price as price,
                c.photoId as photoId,
                (select count(*) from CourseJoin as cj where cj.uid = ? and cj.courseId = c.id) as isJoin
            from Course as c
            where id = ?
            LIMIT 1`;
        let data = [ uid, courseId ];
        return sqlExec(sql, data);
    },
    /*
     * 购买课程接口
     */
    async buyCourse(uid, courseId) {
        let sql = `insert into
            CourseJoin(uid, courseId) (
                select ?, ? where (
                    select count(*) from CourseJoin where uid = ? and courseId = ?
                ) = 0
            )`;
        let data = [ uid, courseId, uid, courseId ];
        return sqlExec(sql, data);
    },
    /*
     * 获取已购买课程列表接口
     */
    async getJoinCourse(uid) {
        let sql = `select
                c.id as id,
                c.name as name,
                c.description as description,
                c.price as price,
                c.photoId as photoId
            from CourseJoin as cj
            left join Course as c on cj.courseId = c.id
            where cj.uid = ?`;
        let data = [ uid ];
        return sqlExec(sql, data);
    },
    async getAllCourse(page) {
        let sql = `select
                c.id as id,
                c.uid as uid,
                c.name as name,
                c.description as description,
                c.price as price
            from Course as c
            LIMIT ?, 50`;
        let data = [ (page-1) * 50 ];
        return sqlExec(sql, data);
    },
    // 发布课程接口
    async publishCourse(uid, name, price, description, imageUrl) {
        let selectedImage = imageUrl;
        let connection;
        // 开启事务
        try {
            connection = await startTransaction();
        } catch (err) {
            throw err;
        }
        // 执行sql
        let result;
        try {
            // 创建图片记录
            let sql1 = `insert into PublicImg(
                    browser, uri, status
                ) VALUES (?, ?, ?)`;
            let data1 = [ 'public', '', '待上传' ];
            result = await sqlExecInTransaction(connection, sql1, data1);
            let insertId = result.insertId;
            // 上传图片
            // 提取 base64 数据部分
            let base64Data = selectedImage.split(',')[1];
            // 将 base64 数据转换为 Buffer
            let buffer = Buffer.from(base64Data, 'base64');
            // 定义 MinIO 的 bucket 名称和对象名称
            let bucketName = 'public';                   // 替换为你的 bucket 名称
            let objectName = `public-${insertId}.png`;   // 替换为你希望在 MinIO 中存储的对象名称
            // 上传 Buffer 到 MinIO
            await minio.minio.putObject(bucketName, objectName, buffer, buffer.length, async function (err, etag) {
                if (err) {
                    await rollbackTransaction(connection);
                    isRollback = true;
                    throw err;
                }
            });
            // 补充图片记录
            let sql2 = `update PublicImg set
                    uri = ?,
                    status = '已完成'
                where id = ?`;
            let data2 = [ objectName, insertId ];
            await sqlExecInTransaction(connection, sql2, data2);
            // 创建圈子记录
            let sql3 = `insert into Course(
                    uid, name, description, price, photoId
                ) VALUES (?, ?, ?, ?, ?)`;
            let data3 = [ uid, name, description, price, insertId ];
            result = await sqlExecInTransaction(connection, sql3, data3);
            commitTransaction(connection);
            return result;
        } catch (err) {
            rollbackTransaction(connection);
            throw err;
        }
    },
    async deleteCourse(uid, courseId) {
        let sql = `delete from Course where id = ?`;
        let data = [ courseId ];
        return sqlExec(sql, data);
    }
};

module.exports = courseDao;